from py2neo import Graph,Node,Relationship,NodeMatcher,RelationshipMatcher
from db_utils.mysql_utils import MysqlInstance
from settings import *
from db_utils.dict_utils import split_values_from_keys
from common.sql_dict import *
from tqdm import tqdm

class createBHPData(object):
    def __init__(self):
        # 建立连接
        link = Graph("http://localhost:7474", username="neo4j", password="123456")
        self.graph = link
        self.matcher = NodeMatcher(graph=self.graph)
        self.relation_matcher = RelationshipMatcher(graph=self.graph)
        self.mysql_obj = MysqlInstance(default_connect)


        #实体映射表
        self.entity_table_dict = {
            "crm_enterprise":["crm_enterprise_id","enterprise_name","enterprise_address"],
            'crm_project':["crm_project_id","project_fullname","project_address","project_province","project_city","project_region","project_description"],
            "crm_project_contactor":["crm_project_contactor_id",'projectcontractor_name','projectcontractor_tel'],
            "crm_tender_announcement":["crm_tender_announcement_id",'project_section_name',"project_section_bidder_qualification_level"]
        }


        self.entity_to_label_dict = {
            "crm_enterprise": entity_label_company,
            'crm_project': entity_label_project,
            "crm_project_contactor":entity_label_company_project_major,
            "crm_tender_announcement": entity_label_project_section
        }



    def clean_graph(self):
        # 清空数据库
        self.graph.delete_all()


    def insert_entitys(self):
        for key,value in self.entity_table_dict.items():
            infos = self.mysql_obj.search_info_from_mysql(key, {},value)
            for info in tqdm(infos):
                info = split_values_from_keys(info,eval("convert_zh_"+ key))

                for sub_key,value in list(info.items()):
                    if not value:
                        del info[sub_key]
                        continue
                    if '名称' in sub_key:
                        info['name'] = value
                    elif "姓名" in sub_key:
                        info['name'] = value
                node = self.matcher.match(self.entity_to_label_dict[key], name=info['name']).first()
                if not node:
                    node = Node(self.entity_to_label_dict[key], **info)
                    self.graph.create(node)

    def insert_relations_toubiao(self):
        '''
        插入公司和项目的投标关系
        :return:
        '''
        sql = 'select crm_enterprise_bidding_id ,PK_crm_tender_announcement_id ,PK_crm_enterprise_id ,is_win_bidding ,win_bidding_time from crm_enterprise_bidding ceb join crm_enterprise ce  on ce.crm_enterprise_id = ceb.PK_crm_enterprise_id join crm_tender_announcement cta on cta.crm_tender_announcement_id = ceb.PK_crm_tender_announcement_id'
        results = self.mysql_obj.execute_sql(sql)
        for info in results:
            tender_node = self.matcher.match(entity_label_project_section, {"crm_tender_announcement_id":info['PK_crm_tender_announcement_id']}).first()
            company_node = self.matcher.match(entity_label_company, {"crm_enterprise_id":info['PK_crm_enterprise_id']}).first()
            relation = self.relation_matcher.match((company_node, tender_node),
                                                   r_type= relation_bidding,properties ={"crm_enterprise_bidding_id":info["crm_enterprise_bidding_id"]}).first()
            if not relation:
                relation_properties = {}
                if info['is_win_bidding']:
                    relation_properties["中标"] = 1
                else:
                    relation_properties['中标'] = 0

                if info['win_bidding_time']:
                    relation_properties['中标时间'] = info['win_bidding_time']
                tnb_to_cli = Relationship(company_node, relation_bidding, tender_node,**relation_properties)
                self.graph.create(tnb_to_cli)

    def insert_realtions_jiuzhi(self):
        '''
        插入公司和项目经理的就职关系
        :return:
        '''
        infos = self.mysql_obj.search_info_from_mysql("crm_enterprise_bidding", {})
        for info in infos:
            if info['PK_crm_enterprise_id'] and info['PK_crm_project_contactor_id']:
                contactor_node = self.matcher.match(entity_label_company_project_major, {"crm_project_contactor_id":info['PK_crm_project_contactor_id']}).first()
                company_node = self.matcher.match(entity_label_company, {"crm_enterprise_id":info['PK_crm_enterprise_id']}).first()
                relation = self.relation_matcher.match((contactor_node,company_node),
                                                       r_type= relation_work_in).first()
                if not relation:
                    tnb_to_cli = Relationship(contactor_node, relation_work_in, company_node)
                    self.graph.create(tnb_to_cli)

            if info['PK_crm_enterprise_id'] and info['PK_crm_project_contactor_id'] and info[
                'PK_crm_project_contactor_id'] and info['is_win_bidding']:
                contactor_node = self.matcher.match(entity_label_company_project_major, {
                    "crm_project_contactor_id": info['PK_crm_project_contactor_id']}).first()
                tender_node = self.matcher.match(entity_label_project_section, {
                    "crm_tender_announcement_id": info['PK_crm_tender_announcement_id']}).first()

                relation = self.relation_matcher.match((contactor_node, tender_node),
                                                       r_type=relation_win_bidding_company_project_major).first()
                if not relation:
                    tnb_to_cli = Relationship(contactor_node, relation_win_bidding_company_project_major, tender_node)
                    self.graph.create(tnb_to_cli)

    def insert_relation_biaoduan(self):
        infos = self.mysql_obj.search_info_from_mysql("crm_tender_announcement", {})
        for info in tqdm(infos):
            project_node = self.matcher.match(entity_label_project,
                                              **{"crm_project_id": info['PK_crm_project_id']}).first()
            section_node = self.matcher.match(entity_label_project_section,**{"crm_tender_announcement_id": info['crm_tender_announcement_id']})

            relation = self.relation_matcher.match((project_node, section_node),
                                                   r_type=relation_section).first()
            if not relation:
                tnb_to_cli = Relationship(project_node, relation_section, section_node)
                self.graph.create(tnb_to_cli)



if __name__ == '__main__':
    a = createBHPData()
    a.clean_graph()
    a.insert_entitys()

    a.insert_relation_biaoduan()

    #a.insert_realtions_jiuzhi()

    #a.insert_relations_toubiao()